package com.sduonline.isdapp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CreateCategoryDao {

	private static ConnectionPool pool;

	public CreateCategoryDao() {
		if (pool == null) {
			pool = ConnectionPool.getInstance();
		}
	}

	public boolean addCategory(int web, String name, String url) {
		Connection conn = pool.getConnection();
		String sql = "insert into isd_category (web,name,url)  values (?,?,?)";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);
			ptmt = conn.prepareStatement(sql);
			ptmt.setInt(1, web);
			ptmt.setString(2, name);
			ptmt.setString(3, url);
			ptmt.execute();
			conn.commit();
			ptmt.close();
			conn.close();

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return false;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int getMaxID() {
		int id = 0;

		Connection conn = pool.getConnection();
		String sql = "select max(id) as id from isd_category ";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);
			ptmt = conn.prepareStatement(sql);

			ResultSet rSet = ptmt.executeQuery();
			if (rSet.next())
				id = rSet.getInt("id");
			rSet.close();
			conn.commit();
			ptmt.close();
			conn.close();

			return id;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return 0;
			}
			return 0;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public boolean isExist(int web, String name) {
		boolean flag = false;
		Connection conn = pool.getConnection();
		String sql = "select *  from isd_category where web =? and name=? ";
		PreparedStatement ptmt = null;

		try {
			conn.setAutoCommit(false);
			ptmt = conn.prepareStatement(sql);
			ptmt.setInt(1, web);
			ptmt.setString(2, name);
			ResultSet rSet = ptmt.executeQuery();
			if (rSet.next())
				flag = true;
			rSet.close();
			conn.commit();
			ptmt.close();
			conn.close();

			return flag;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			return flag;
		} finally {
			try {
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
